#include "head.h"

sqlite3 *db = NULL;  //数据库句柄

void do_login(int sockfd, MSG *pbuf)
{
	char **dbResult; 
    char *errmsg;
    int nRow, nColumn;
	char sql[64] = {0};	
	sprintf(sql,"select * from staff where id = %d;",pbuf->info.id);
	//数据库进行条件查询
	if(sqlite3_get_table(db,sql,&dbResult,&nRow,&nColumn,&errmsg) == SQLITE_OK)
	{
	printf("login %s\n",dbResult[11]);
		
		if(nRow != 0 && nColumn!= 0)
		{
				if(strcmp(dbResult[11],pbuf->passwd) != 0)
				{
					pbuf->ret = 0;
					strcpy(pbuf->data,"密码错误");
				}
				else
				{
					pbuf->ret = 1;
					strcpy(pbuf->data,"登陆成功");
					if((pbuf -> info.id) > 100)
					{
						pbuf -> type = 100;
					}
					else
					{
						pbuf -> type = 101;

					}
				}
			
		}
		else 
		{
			printf("no this user\n");
			//查询失败 ret = -1, data = "登录失败“
			pbuf->ret = 0;
			strcpy(pbuf->data,"用户名错误");
		}
	}
	else
	{
		printf("用户不存在\n");
		pbuf->ret = 0;
		strcpy(pbuf->data,"用户不存在");
	}
	
	//结果反馈给客户端
	send(sockfd,pbuf,sizeof(MSG),0);
}
void do_staff_change_passwd(int sockfd,MSG *pbuf)
{
   	char *errmsg;
	char sql[64] = {'\0'};
	sprintf(sql,"update staff set passwd = '%s' where id = %d",pbuf->passwd,pbuf->info.id);
	// 根据 id 修改数据库内容为 passwd
    if( sqlite3_exec(db,sql,NULL,NULL,&errmsg) == SQLITE_OK)
	{
		//修改成功 ret = 0，data = “修改密码完成”
		pbuf->ret = 1;
		strcpy(pbuf->data,"修改密码完成");
	}
	else 
	{
		//查询失败 ret = -1, data = "修改密码失败“
		pbuf->ret = 0;
		strcpy(pbuf->data,"修改密码失败");
	}
	send(sockfd,(void *)&pbuf,sizeof(MSG),0);
}

void do_staff_query(int sockfd, MSG *pbuf)
{
    int pnRow,pnColumn;
	char sqlstr[128];
	char *errmsg, **result;
	snprintf(sqlstr,sizeof(sqlstr)-1,"SELECT * FROM staff where id =%d",pbuf->info.id);
	if(sqlite3_get_table(db,sqlstr,&result,&pnRow,&pnColumn,&errmsg)!=SQLITE_OK)
    {
		pbuf->ret = 0;
    	snprintf(pbuf->data,sizeof(pbuf->data),"用户信息查询失败");
    	sqlite3_free(errmsg);
    }
	else
	{ 
		pbuf->ret = 1;
		strcpy(pbuf->data,"查询信息成功");
		pbuf->info.id = atoi(result[6]);
		strcpy(pbuf->info.name,result[7]);
		strcpy(pbuf->info.addr,result[8]);
		pbuf->info.age = atoi(result[9]);
		pbuf->info.salary = atof(result[10]);
	}
    send(sockfd,pbuf,sizeof(MSG),0); 
}

void do_admin_query(int sockfd, MSG *pbuf)
{
	int pnRow,pnColumn;
	char sqlstr[128];
	char *errmsg, **result;
	snprintf(sqlstr,sizeof(sqlstr)-1,"SELECT * FROM staff where id =%d",pbuf->info.id);
	if(sqlite3_get_table(db,sqlstr,&result,&pnRow,&pnColumn,&errmsg)!=SQLITE_OK)
    {
		pbuf->ret = 0;
    	snprintf(pbuf->data,sizeof(pbuf->data),"用户信息查询失败");
    	sqlite3_free(errmsg);
    }
	else
	{ 
		pbuf->ret = 1;
		strcpy(pbuf->data,"查询员工信息成功");
		pbuf->info.id = atoi(result[6]);
		strcpy(pbuf->info.name,result[7]);
		strcpy(pbuf->info.addr,result[8]);
		pbuf->info.age = atoi(result[9]);
		pbuf->info.salary = atof(result[10]);
	}
    send(sockfd,pbuf,sizeof(MSG),0);

	
}

void do_admin_del(int sockfd, MSG *pbuf)
{
	char sqlstr[128];
	char *errmsg;
	snprintf(sqlstr,sizeof(sqlstr)-1,"delete from staff where id=\"%d\"",pbuf->info.id);
	if(sqlite3_exec(db,sqlstr,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		pbuf->ret = 0;
		snprintf(pbuf->data,sizeof(pbuf->data)-1,"Delete failed:%s",errmsg);
    	sqlite3_free(errmsg);
	}
	else
	{
		pbuf->ret = 1;
		strncpy(pbuf->data,"删除成功",sizeof(pbuf->data)-1);
		
	}
	send(sockfd,pbuf,sizeof(MSG),0);	
}

void do_admin_add(int sockfd,MSG *pbuf)
{
	char sqlstr[128];
	char *errmsg;
	char **result;
	int nrow = 0,ncolumn = 0;
	sprintf(sqlstr,"select * from staff where id = %d;",pbuf->info.id);
	sqlite3_get_table(db,sqlstr,&result,&nrow,&ncolumn,&errmsg);
	if(nrow != 0 || ncolumn != 0)
	{
		pbuf->ret = 0;
		strcpy(pbuf->data,"此id已使用");
	}
	else
	{
		snprintf(sqlstr,sizeof(sqlstr)-1,"insert into staff(id,name,addr,age,salary,passwd) values(\"%d\",\"%s\",\"%s\",\"%d\",\"%f\",\"%s\")"
				,pbuf->info.id,pbuf->info.name,pbuf->info.addr,pbuf->info.age,pbuf->info.salary,pbuf->passwd);
		if(sqlite3_exec(db,sqlstr,NULL,NULL,&errmsg) != SQLITE_OK)
		{
			pbuf->ret = 0;
			snprintf(pbuf->data,sizeof(pbuf->data)-1,"Add failed:%s",errmsg);
			sqlite3_free(errmsg);
		}
		else
		{
			pbuf->ret = 1;
			strncpy(pbuf->data,"员工信息添加成功",sizeof(pbuf->data)-1);

		}
	}
	send(sockfd,&pbuf,sizeof(MSG),0);
}

void do_admin_changeInfo(int sockfd,MSG *pbuf)
{
	char *errmsg;
	char sqlstr[128];
	snprintf(sqlstr,sizeof(sqlstr)-1,"update staff set id = %d,name = '%s',addr = '%s',age = %d,salary = %f,passwd = '%s';",pbuf->info.id,pbuf->info.name
	,pbuf->info.addr,pbuf->info.age,pbuf->info.salary,pbuf->passwd);
	if(sqlite3_exec(db,sqlstr,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		pbuf->ret = 0;
		strcpy(pbuf->data,"修改失败");
	}
	else
	{
		pbuf->ret = 1;
		strcpy(pbuf->data,"修改成功");
	}
	
	send(sockfd,pbuf,sizeof(MSG),0);
}

void *handler(void * arg)
{
     MSG msg;
    int n;
    int connfd = *((int *)arg);
    while(1)
    {
        n = recv(connfd, &msg, sizeof(MSG), 0);
        printf("name = %s\n", msg.name);
        if(n == 0){  //客户端退出
            printf("客户端退出\n");
			pthread_exit(NULL);
            close(connfd);
        }else if(n > 0){  //收到信息
            printf("recv type = %d\n", msg.type);
            switch (msg.type) {
            case LOGIN:  //登录
                do_login(connfd, &msg);
                break;
            case STAFF_CHANGE_PASSWD: //修改密码
                do_staff_change_passwd(connfd, &msg);
                break;
            case STAFF_QUERY: //普通用户查询
                do_staff_query(connfd, &msg);
                break;
            case ADMIN_QUERY: //管理员查询
                do_admin_query(connfd, &msg);
                break;
            case ADMIN_DEL_STAFF: //管理员删除用户
                do_admin_del(connfd, &msg);
                break;
            case ADMIN_ADD_STAFF: //管理员增加用户
                do_admin_add(connfd, &msg);
			case ADMIN_CHANGE_INFO: //管理员修改用户信息
				do_admin_changeInfo(connfd, &msg);
                break;
            default:
                break;
            }
        }else{
            printf("abc");
            break;
        }
    }
    close(connfd);
    pthread_exit(NULL);
}

int main(int argc, char *argv[])
{
    int listenfd, connfd;
    struct sockaddr_in myaddr;
	char *errmsg;
	char **result;
	pthread_t num_pthread;
	int len = sizeof(struct sockaddr);
	int nrow,ncolumn;
    if (argc < 3)
    {
        printf("Usage : %s <ip> <port>\n", argv[0]);
        exit(-1);
    }

    //打开数据库
    if(sqlite3_open(DATABASE, &db) < 0){
        printf("fail to sqlite3_open : %s\n", sqlite3_errmsg(db));
        return -1;
    }
	sqlite3_exec(db,"create table staff(id int,name text,addr text,age int,salary real,passwd text);",NULL,NULL,&errmsg);
	
	sqlite3_get_table(db,"select * from staff where name = 'admin';",&result,&nrow,&ncolumn,&errmsg);
	if(nrow == 0 || ncolumn == 0)
	{
	sqlite3_exec(db,"insert into staff values ('1','admin','','20','5000','123456');",NULL,NULL,&errmsg);
	}
	//创建服务器socket
    listenfd = socket(PF_INET, SOCK_STREAM, 0);
    if(listenfd < 0){
        perror("fail to socket");
        exit(-1);
    }
    bzero(&myaddr, sizeof(myaddr));
    myaddr.sin_family = PF_INET;
    myaddr.sin_port = htons(atoi(argv[2]));
    myaddr.sin_addr.s_addr = inet_addr(argv[1]);
    if (bind(listenfd, (SA *)&myaddr, sizeof(myaddr)) < 0)
    {
        perror("fail to bind");
        exit(-1);
    }

    //接受客户端请求，创建子线程
	connfd = listen(listenfd,50);	
	while(1)
	{
		connfd = accept(listenfd,(struct sockaddr *)&myaddr,&len); 
		printf("connect user.\n");
		pthread_create(&num_pthread, NULL, handler, (void *)&connfd);
		
	}
}
